/*      *******************************************************
 *      
 *      A D D I N G   A N D   R E T R I E V I N G   D A T A
 *      
 *      *******************************************************/

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class HelloWorldExample extends HttpServlet {
    
    private String responseMessage;
    private Connection theConnection;
    private Statement theStatement;
    private ResultSet theResultSet;
    private String Sql[];       //  Array of SQL Statements (To add to database)
    private String call;
    private String species;
    private String image;
    private String latitude;
    private String longitude;
    private String user;
    private String place;
    private String recordId;
    private boolean success;
    private boolean allParameters;
    private boolean addReq;
    
    @Override
    public void init() {
        Sql = new String[1];
        addReq = allParameters = true;
    }
    
    @Override
    public void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, 
            IOException {
        
        doGet(request,response);
    }

    @Override
    public void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, 
            IOException {
        
        serviceRequest(request,response);
    }
    
    private void serviceRequest(HttpServletRequest request,
            HttpServletResponse response) throws ServletException,
            IOException {
        
/*      ****************************
 *      Read input/request...
 *      ****************************/
        

        call = request.getParameter("call");
        species = request.getParameter("species");
        latitude = request.getParameter("lat");
        longitude = request.getParameter("lon");
        user = request.getParameter("user");
        place = request.getParameter("place");
        image = request.getParameter("link");

       
/*      ****************************
 *      Processing request...
 *      ****************************/

        //      Index of Sql array..
        int queryIndex=0;
                
        if (call.equalsIgnoreCase("ADD"))
            formulateQuery2(queryIndex);        
        else if (call.equalsIgnoreCase("GET"))
            formulateQuery(queryIndex);        

        responseMessage = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n<marker>\n";
        if(allParameters)
        {
//          responseMessage = "Data read:\n\t"+call+" "+species+" "+image+" "+latitude+" "+longitude+" "+place+" "+user+"\n\n<marker>\n";
  
            connectDatabase();
/*      ****************************
 *      Sending response...
 *      ****************************/
        
//            if(success && addReq)
//                    responseMessage += "\t<mark id=\""+recordId+"\" lat=\""+latitude +"\" lon=\""+ longitude 
//                            +"\" species=\""+species +"\" user=\""+ user+"\" place=\""
//                            +place+"\" link=\""+image+"\"></mark>\n";
        }
            responseMessage +="</marker>\n";
            //responseMessage +="\nSuccess: "+success;

            response.setContentType("text/xml");
    //        response.setContentLength(responseMessage.length());
//        }
//        else
//        {
//            responseMessage = "Incomplete data!";
//            response.setContentType("text/xml");
//        }
        
        PrintWriter out = response.getWriter();
        out.println(responseMessage);
        out.close();
        out.flush();
    }
    
    private void formulateQuery(int no){
        
/*      ****************************
 *      Formulate queries...
 *      ****************************/
        addReq = false;
        allParameters=true; 
        Sql[no] = "SELECT * FROM Sight;";// WHERE Lat between "+species+" and "+image+" AND Lon between "+latitude+" and "+longitude+";";
    }
    private void formulateQuery2(int no){
        
/*      ****************************
 *      Formulate queries...
 *      ****************************/
        if(species == null || latitude == null || longitude == null || user == null || place == null || image == null)
             allParameters=false; 
        else
        {    
            Sql[no] = "INSERT INTO Sight (Species, Lat, Lon, User, Place, Pic) VALUES(";
            if(species.length() != 0)        
                   Sql[no] += "\'"+species+"\',";
            else allParameters=false;
            if(latitude.length() != 0)        
                   Sql[no] += latitude+",";
            else allParameters=false;
            if(longitude.length() != 0)        
                   Sql[no] += longitude+",";
            else allParameters=false;
            if(user.length() != 0)        
                   Sql[no] += "\'"+user+"\',";
            else allParameters=false;
            if(place.length() != 0)        
                   Sql[no] += "\'"+place+"\',";
            else allParameters=false;
            if(image.length() != 0)        
                   Sql[no] += "\'"+image+"\');";
            else
                Sql[no] += "\'http://www.faro.com/FaroIP/Files/Image/ALL/misc/questionmark.jpg\'";

        }
    }

    private void connectDatabase(){

/*      ****************************
 *      Connecting to Database...
 *      ****************************/

        try{
//              Loading Sun's JDBC ODBC Driver
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//              Establishing connection with the Database
                theConnection = DriverManager.getConnection(
                        "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};" +
                        "DBQ=C:\\Users\\Apy\\Documents\\Desktop Files\\Google\\sight.mdb;DriverID=22;READONLY=false");
                theStatement=theConnection.createStatement();

                executeQueries();
                
                theStatement.close();               //Close statement
                theConnection.close();              //Close database Connection 
	}
        catch(Exception e)
        {
		responseMessage += e.getMessage();     //Print trapped error.
	}
    }

    private void executeQueries(){
        
/*      ****************************
 *      Executing queries...
 *      ****************************/

        int i=0;
        try {
            for (; i<Sql.length && Sql[i]!=""; i++) {
            
                    boolean hasResults = theStatement.execute(Sql[i]);
                    success = true;
                    if(hasResults){

                        theResultSet = theStatement.executeQuery(Sql[i]);
                        evaluateResultSet();
                    }
            }
        }
        catch (Exception e) {
            success = false;
            responseMessage += "\nProblems with SQL!\n"+e.getMessage()+Sql[i];
        }
    }
   
    private void evaluateResultSet(){
        
/*      ****************************
 *      Appending response message...
 *      ****************************/

        try {
            success = true;
            while(theResultSet.next()){
                
                String id = theResultSet.getString("ID");
                String s = theResultSet.getString("Species");
                String lat = theResultSet.getString("Lat");
                String lon = theResultSet.getString("Lon");
                String user = theResultSet.getString("User");
                String place = theResultSet.getString("Place");
                String img = theResultSet.getString("Pic");

                responseMessage += "\t<mark id=\""+id+"\" lat=\""+lat +"\" lon=\""+ lon 
                        +"\" species=\""+s +"\" user=\""+ user+"\" place=\""
                        +place+"\" link=\""+img+"\"></mark>\n";
            }
            
        }
        catch (Exception e) {
            success = true;
            e.printStackTrace();
            responseMessage += e.getMessage();     //Print trapped error.
        }
    }
}
